package com.api.gbhg.liuwenbo.version2.api;

import com.alibaba.fastjson.JSON;
import com.api.gbhg.liuwenbo.version2.dao.DataBaseUtil2;
import com.api.gbhg.liuwenbo.version2.pojo.BudgetSectionIncome2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description 预算科收入预算表excel导出
 * @Author lwb
 * @Date 21.12.3
 */
@Path("gb/BudSecInExport2")
public class BudgetSectionIncomeExportApi2 {

    BudgetSectionIncomeApi2 bsi = new BudgetSectionIncomeApi2();

    private int thisIndex = 3;
    private HSSFCellStyle fontStyle = null;//正常字体大小
    private HSSFCellStyle fontCenterStyle = null;//字体大小跟居中
    private HSSFCellStyle cellStyle = null;//字体大小跟居中
    private BigDecimal zysnxj = new BigDecimal("0");//中央上年小计
    private BigDecimal zybnxj = new BigDecimal("0");//中央本年小计
    private BigDecimal zyzj = new BigDecimal("0");//中央总计
    private BigDecimal snjzysapxj = new BigDecimal("0");//上年结转预算安排
    private BigDecimal bnsrysapxj = new BigDecimal("0");//本年收入预算安排
    private BigDecimal hj1zj = new BigDecimal("0");//合计1
    private BigDecimal snjzwapxj = new BigDecimal("0");//上年结转未安排
    private BigDecimal bnsrwapxj = new BigDecimal("0");//本年收入未安排
    private BigDecimal hj2zj = new BigDecimal("0");//合计2总计
    private BigDecimal snjzyszcxj = new BigDecimal("0");//上年结转预算支出
    private BigDecimal bnsryszcxj = new BigDecimal("0");//本年收入预算支出
    private BigDecimal hj3zj = new BigDecimal("0");//合计3总计

    private BigDecimal fzysnxj = new BigDecimal("0");//非中央上年小计
    private BigDecimal fzybnxj = new BigDecimal("0");//非中央本年小计
    private BigDecimal fzyzj = new BigDecimal("0");//非中央总计
    private BigDecimal fzysnjzysapxj = new BigDecimal("0");//非中央上年结转预算安排
    private BigDecimal fzybnsrysapxj = new BigDecimal("0");//非中央本年收入预算安排
    private BigDecimal fzyhj1zj = new BigDecimal("0");//非中央合计1
    private BigDecimal fzysnjzwapxj = new BigDecimal("0");//非中央上年结转未安排
    private BigDecimal fzybnsrwapxj = new BigDecimal("0");//非中央本年收入未安排
    private BigDecimal fzyhj2zj = new BigDecimal("0");//非中央合计2总计
    private BigDecimal fzysnjzyszcxj = new BigDecimal("0");//非中央上年结转预算支出
    private BigDecimal fzybnsryszcxj = new BigDecimal("0");//非中央本年收入预算支出
    private BigDecimal fzyhj3zj = new BigDecimal("0");//非中央合计3总计
    private BigDecimal fzysjsrzj = new BigDecimal("0");//非中央实际收入总计

    private Map<String,Object> msgMap ;

    @POST
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String exportExcel(@Context HttpServletResponse response, Map<String,Object> param){
        msgMap = new HashMap<>();
        if (param == null ||!param.containsKey("nf") || !param.containsKey("dw")){
            msgMap.put("code","500");
            msgMap.put("msg","缺少参数 请检查参数是否完整！");
            msgMap.put("data","");
            return JSON.toJSONString(msgMap);
        }

        Map<String, Object> paramFormat = bsi.paramFormat(param);
        String s = downLoadExcel(response, paramFormat);

        return s;
    }


    /**
     * 浏览器导出
     * @param response
     * @param param
     */
    public String downLoadExcel(HttpServletResponse response,Map<String,Object> param)  {
        List<BudgetSectionIncome2> czbksrData = bsi.getCzbksrData(param);
        List<BudgetSectionIncome2> fczbksrData = bsi.getFczbksrData(param);

        final Logger logger = LoggerFactory.getLogger(BudgetSectionIncomeExportApi2.class);

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("预算科收入预算表");


            //新增数据行，并且设置单元格数据
            HSSFRow row = sheet.createRow(0);
            //定义单元格风格
            cellStyle = workbook.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font = workbook.createFont();
            //设置字体名称
//            font.setFontName("华文行楷");
            //设置字号
            font.setFontHeightInPoints((short)11);
            //设置是否为斜体
            font.setItalic(false);
            //设置是否加粗
            font.setBold(true);

            //设置字体颜色
            font.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            cellStyle.setFont(font);
            row.setHeightInPoints(20);
            //设置默认的列宽度
            sheet.setDefaultColumnWidth(9);
            sheet.setColumnWidth(0,500*10);
            sheet.setColumnWidth(1,1200*10);
            sheet.setColumnWidth(2,500*10);
            sheet.setColumnWidth(3,500*10);
            sheet.setColumnWidth(4,500*10);
            sheet.setColumnWidth(5,500*10);
            sheet.setColumnWidth(6,550*10);
            sheet.setColumnWidth(7,500*10);
            sheet.setColumnWidth(8,500*10);
            sheet.setColumnWidth(9,500*10);
            sheet.setColumnWidth(10,500*10);
            sheet.setColumnWidth(11,500*10);
            sheet.setColumnWidth(12,500*10);
            sheet.setColumnWidth(13,500*10);
            sheet.setColumnWidth(14,500*10);
            sheet.setColumnWidth(15,500*10);
            sheet.setColumnWidth(16,600*10);
            sheet.setColumnWidth(17,600*10);
            sheet.setColumnWidth(18,500*10);
            sheet.setColumnWidth(19,500*10);
            sheet.setColumnWidth(20,500*10);

            //合并 四个参数（分别为起始行，结束行，起始列，结束列）
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
            sheet.addMergedRegion(region);

            String dwName = DataBaseUtil2.getSubcompanyNameById(param.get("dw").toString());

            //添加excel表头第一行
            HSSFCell cellNf = row.createCell(0);
            cellNf.setCellStyle(cellStyle);
            HSSFRichTextString nf = new HSSFRichTextString("年份："+param.get("nf")+"   "+"收入单位："+dwName);
            cellNf.setCellValue(nf);

            HSSFCell cellJedw = row.createCell(6);
            cellJedw.setCellStyle(cellStyle);
            HSSFRichTextString jedw = new HSSFRichTextString("单位:万元");
            cellJedw.setCellValue(jedw);
            //-----------表头添加结束

            //创建coloTitleStyle--------------------
            //设置背景颜色的style
            HSSFCellStyle coloTitleStyle = workbook.createCellStyle();
            coloTitleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            coloTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            coloTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //设置字段style
            HSSFFont fontTitle = workbook.createFont();
            fontTitle.setFontHeightInPoints((short)11);
            coloTitleStyle.setFont(fontTitle);
            //end--------------------

            //创建字体大小无居中style----------------
            fontStyle = workbook.createCellStyle();
            fontStyle.setFont(fontTitle);
            //end----------------------

            //创建字体大小水平居中style----------------
            fontCenterStyle = workbook.createCellStyle();
            fontCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            fontCenterStyle.setAlignment(HorizontalAlignment.CENTER);
            fontCenterStyle.setFont(fontTitle);
            //end----------------------

            //创建财政拨款标题行
            HSSFRow row1 = sheet.createRow(1);
            row1.setHeightInPoints(20);
            HSSFCell cellczbksr = row1.createCell(0);
            cellczbksr.setCellStyle(coloTitleStyle);
            HSSFRichTextString czbksr = new HSSFRichTextString("财政拨款收入");
            cellczbksr.setCellValue(czbksr);

            //合并行
            CellRangeAddress regionT1 = new CellRangeAddress(1, 1, 0, 18);
            sheet.addMergedRegion(regionT1);
            //标题结束-----------------------------------


            //开始设置财政拨款收入标题
            String[] headers1 = {"科目编码","科目名称","上年结转","本年收入","合计","上年结转预算安排","本年收入预算安排","合计","上年结转未安排",
                    "本年收入未安排","合计","上年结转预算支出","本年收入预算支出","支出合计","上年结转预算执行率","本年收入预算执行率","预算执行率合计"};

            HSSFRow row2 = sheet.createRow(2);
            for (int i = 0; i < headers1.length; i++) {
                HSSFCell cell = null;
                if (i==4){
                    cell = row2.createCell(i+2);
                }else if(i == 3){
                    cell = row2.createCell(i+1);
                }else if(i>4){
                    //下标已超前两位所以需要+2
                    cell = row2.createCell(i+2);
                }else{
                    cell = row2.createCell(i);
                }
                cell.setCellStyle(cellStyle);
                if (i==2 || i==3){
                    int j ;
                    if (i==3)
                        j = 4;
                    else
                        j = i;

                    row2.createCell(j+1);
                    cell.setCellValue(headers1[i]);
                    CellRangeAddress tmpregion = new CellRangeAddress(2, 2, j, j+1);
                    sheet.addMergedRegion(tmpregion);
                }else{
                    cell.setCellValue(headers1[i]);
                }
            }
            //---------------开始写入财政拨款收入---------------------------------------
            String space = "      ";//空格
            int startIndex = 3;
            for (BudgetSectionIncome2 bean:czbksrData) {
                HSSFRow czbksrRow = sheet.createRow(thisIndex);
                czbksrRow.setHeightInPoints(20);

                HSSFCell kmbm = czbksrRow.createCell(0);
                kmbm.setCellValue(bean.getName());

                HSSFCell kmdm = czbksrRow.createCell(1);
                kmdm.setCellValue(bean.getKmmc());

                czbksrRow.createCell(1).setCellValue(bean.getKmmc());

                czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
                czbksrRow.createCell(3);
                //合并行上年
                CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
                sheet.addMergedRegion(regionSn);

                czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
                czbksrRow.createCell(5);
                //合并行本年
                CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
                sheet.addMergedRegion(regionBn);

                czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

                czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

                czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

                czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

                czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

                thisIndex++;
                List<BudgetSectionIncome2> xj = bean.getXj();
                createZyczbksrXj(sheet,xj,space);

                //计算小计 总计
                zysnxj = zysnxj.add(new BigDecimal(bean.getSnjz()));
                zybnxj = zybnxj.add(new BigDecimal(bean.getBnsr()));
                zyzj = zyzj.add(new BigDecimal(bean.getHj()));
                snjzysapxj = snjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
                bnsrysapxj = bnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
                hj1zj = hj1zj.add(new BigDecimal(bean.getHj1()));
                snjzwapxj = snjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
                bnsrwapxj = bnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
                hj2zj = hj2zj.add(new BigDecimal(bean.getHj2()));
                snjzyszcxj = snjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
                bnsryszcxj = bnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
                hj3zj = hj3zj.add(new BigDecimal(bean.getHj3()));

                //设置表格中字体
                for (int k = 0; k < 19; k++) {
                    if (k == 1)
                        czbksrRow.getCell(k).setCellStyle(fontStyle);
                    else
                        czbksrRow.getCell(k).setCellStyle(fontCenterStyle);
                }

                if (startIndex != thisIndex -1){
                    CellRangeAddress regionKmbm = new CellRangeAddress(startIndex, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(regionKmbm);
                }
                startIndex = thisIndex;
            }

            HSSFRow czbksrEndRow = sheet.createRow(thisIndex);
            czbksrEndRow.setHeightInPoints(20);
            HSSFCell zyCell0 = czbksrEndRow.createCell(0);
            zyCell0.setCellValue("单位："+dwName);
            zyCell0.setCellStyle(fontCenterStyle);

            //小计总计开始赋值
            HSSFCell zyCell2 = czbksrEndRow.createCell(2);
            czbksrEndRow.createCell(3);
            zyCell2.setCellValue("小计:"+zysnxj.toString());
            CellRangeAddress regZysnxj = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regZysnxj);
            zyCell2.setCellStyle(cellStyle);

            HSSFCell zyCell4 = czbksrEndRow.createCell(4);
            czbksrEndRow.createCell(5);
            zyCell4.setCellValue("小计:"+zybnxj.toString());
            CellRangeAddress regZybnxj = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regZybnxj);
            zyCell4.setCellStyle(cellStyle);

            HSSFCell zyCell6= czbksrEndRow.createCell(6);
            zyCell6.setCellValue("总计:"+zyzj.toString());
            zyCell6.setCellStyle(cellStyle);

            HSSFCell zyCell7= czbksrEndRow.createCell(7);
            zyCell7.setCellValue("小计:"+snjzysapxj.toString());
            zyCell7.setCellStyle(cellStyle);

            HSSFCell zyCell8= czbksrEndRow.createCell(8);
            zyCell8.setCellValue("小计:"+bnsrysapxj.toString());
            zyCell8.setCellStyle(cellStyle);

            HSSFCell zyCell9= czbksrEndRow.createCell(9);
            zyCell9.setCellValue("总计:"+hj1zj.toString());
            zyCell9.setCellStyle(cellStyle);

            HSSFCell zyCell10= czbksrEndRow.createCell(10);
            zyCell10.setCellValue("小计:"+snjzwapxj.toString());
            zyCell10.setCellStyle(cellStyle);

            HSSFCell zyCell11= czbksrEndRow.createCell(11);
            zyCell11.setCellValue("小计:"+bnsrwapxj.toString());
            zyCell11.setCellStyle(cellStyle);

            HSSFCell zyCell12= czbksrEndRow.createCell(12);
            zyCell12.setCellValue("总计:"+hj2zj.toString());
            zyCell12.setCellStyle(cellStyle);

            HSSFCell zyCell13= czbksrEndRow.createCell(13);
            zyCell13.setCellValue("小计:"+snjzyszcxj.toString());
            zyCell13.setCellStyle(cellStyle);

            HSSFCell zyCell14= czbksrEndRow.createCell(14);
            zyCell14.setCellValue("小计:"+bnsryszcxj.toString());
            zyCell14.setCellStyle(cellStyle);

            HSSFCell zyCell15= czbksrEndRow.createCell(15);
            zyCell15.setCellValue("总计:"+hj3zj.toString());
            zyCell15.setCellStyle(cellStyle);

            thisIndex++;

            //-----------开始写入非财政拨款收入（非中央）-----------------------------------------------------

            //创建非财政拨款标题行
            HSSFRow fzyTitlerow = sheet.createRow(thisIndex);
            fzyTitlerow.setHeightInPoints(20);
            HSSFCell cellfczbksr = fzyTitlerow.createCell(0);
            cellfczbksr.setCellStyle(coloTitleStyle);
            HSSFRichTextString fczbksr = new HSSFRichTextString("非财政拨款收入");
            cellfczbksr.setCellValue(fczbksr);

            //合并行
            CellRangeAddress regionT2 = new CellRangeAddress(thisIndex, thisIndex, 0, 19);
            sheet.addMergedRegion(regionT2);
            thisIndex++;

            //开始设置非财政拨款收入标题
            String[] headers2 = {"收入分类","收入项目","上年结转","本年收入","合计","上年结转预算安排","本年收入预算安排","合计","上年结转未安排",
                    "本年收入未安排","合计","上年结转预算支出","本年收入预算支出","支出合计","上年结转预算执行率","本年收入预算执行率","预算执行率合计","本年实际收入"};

            HSSFRow row3 = sheet.createRow(thisIndex);
            for (int i = 0; i < headers2.length; i++) {
                HSSFCell cell = null;
                if (i==4){
                    cell = row3.createCell(i+2);
                }else if(i == 3){
                    cell = row3.createCell(i+1);
                }else if(i>4){
                    //下标已超前两位所以需要+2
                    cell = row3.createCell(i+2);
                }else{
                    cell = row3.createCell(i);
                }
                cell.setCellStyle(cellStyle);
                if (i==2 || i==3){
                    int j ;
                    if (i==3)
                        j = 4;
                    else
                        j = i;

                    row3.createCell(j+1);
                    cell.setCellValue(headers2[i]);
                    CellRangeAddress tmpregion = new CellRangeAddress(thisIndex, thisIndex, j, j+1);
                    sheet.addMergedRegion(tmpregion);
                }else{
                    cell.setCellValue(headers2[i]);
                }
            }

            thisIndex++;
            //开始写入非财政拨款收入数据------------------------------------
            startIndex = thisIndex;
            for (BudgetSectionIncome2 bean:fczbksrData) {
                List<BudgetSectionIncome2> xj = bean.getXj();
                createFzyczbksrXm(sheet,xj);
                HSSFCell fzyCell0 = sheet.getRow(startIndex).createCell(0);
                fzyCell0.setCellValue(bean.getName());
                fzyCell0.setCellStyle(fontCenterStyle);

                if (startIndex != thisIndex-1){
                    CellRangeAddress regionKmbm = new CellRangeAddress(startIndex, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(regionKmbm);
                }
                startIndex = thisIndex;
            }

            HSSFRow fczbksrEndRow = sheet.createRow(thisIndex);
            fczbksrEndRow.setHeightInPoints(20);
            HSSFCell fzyCell0 = fczbksrEndRow.createCell(0);
            fzyCell0.setCellValue("单位："+dwName);
            fzyCell0.setCellStyle(fontCenterStyle);

            HSSFCell fzyCell2 = fczbksrEndRow.createCell(2);
            fczbksrEndRow.createCell(3);
            fzyCell2.setCellValue("小计:"+fzysnxj.toString());
            CellRangeAddress regFzysnxj = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regFzysnxj);
            fzyCell2.setCellStyle(cellStyle);

            HSSFCell fzyCell4 = fczbksrEndRow.createCell(4);
            fczbksrEndRow.createCell(5);
            fzyCell4.setCellValue("小计:"+fzybnxj.toString());
            CellRangeAddress regFzybnxj = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regFzybnxj);
            fzyCell4.setCellStyle(cellStyle);

            HSSFCell fzyCell6= fczbksrEndRow.createCell(6);
            fzyCell6.setCellValue("总计:"+fzyzj.toString());
            fzyCell6.setCellStyle(cellStyle);

            HSSFCell fzyCell7= fczbksrEndRow.createCell(7);
            fzyCell7.setCellValue("小计:"+fzysnjzwapxj.toString());
            fzyCell7.setCellStyle(cellStyle);

            HSSFCell fzyCell8= fczbksrEndRow.createCell(8);
            fzyCell8.setCellValue("小计:"+fzybnsrwapxj.toString());
            fzyCell8.setCellStyle(cellStyle);

            HSSFCell fzyCell9= fczbksrEndRow.createCell(9);
            fzyCell9.setCellValue("合计:"+fzyhj1zj.toString());
            fzyCell9.setCellStyle(cellStyle);

            HSSFCell fzyCell10= fczbksrEndRow.createCell(10);
            fzyCell10.setCellValue("小计:"+fzysnjzwapxj.toString());
            fzyCell10.setCellStyle(cellStyle);

            HSSFCell fzyCell11= fczbksrEndRow.createCell(11);
            fzyCell11.setCellValue("小计:"+fzybnsrwapxj.toString());
            fzyCell11.setCellStyle(cellStyle);

            HSSFCell fzyCell12= fczbksrEndRow.createCell(12);
            fzyCell12.setCellValue("合计:"+fzyhj2zj.toString());
            fzyCell12.setCellStyle(cellStyle);

            HSSFCell fzyCell13= fczbksrEndRow.createCell(13);
            fzyCell13.setCellValue("小计:"+fzysnjzyszcxj.toString());
            fzyCell13.setCellStyle(cellStyle);

            HSSFCell fzyCell14= fczbksrEndRow.createCell(14);
            fzyCell14.setCellValue("小计:"+fzybnsryszcxj.toString());
            fzyCell14.setCellStyle(cellStyle);

            HSSFCell fzyCell15= fczbksrEndRow.createCell(15);
            fzyCell15.setCellValue("合计:"+fzyhj3zj.toString());
            fzyCell15.setCellStyle(cellStyle);

            HSSFCell fzyCell19= fczbksrEndRow.createCell(19);
            fzyCell19.setCellValue("合计:"+fzysjsrzj.toString());
            fzyCell19.setCellStyle(cellStyle);

            thisIndex++;


            //-----------开始写入非财政拨款收入（非中央）-----------------------------------------------------

            //创建非财政拨款标题行
            HSSFRow srqkTitlerow = sheet.createRow(thisIndex);
            srqkTitlerow.setHeightInPoints(20);
            HSSFCell cellSrqk = srqkTitlerow.createCell(0);
            cellSrqk.setCellStyle(coloTitleStyle);
            HSSFRichTextString srqk = new HSSFRichTextString("收入预算情况");
            cellSrqk.setCellValue(srqk);

            //合并行
            CellRangeAddress regionT3 = new CellRangeAddress(thisIndex, thisIndex, 0, 20);
            sheet.addMergedRegion(regionT3);
            thisIndex++;

            createSrqk(sheet);


            //设置要导出的文件的名字
            String downFilename  = "info"  + ".xls";
            //服务器发给浏览器的数据是使用UTF-8编码
            response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");

            //设置浏览器的编码为UTF-8
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downFilename,"UTF-8"));

            response.flushBuffer();
            workbook.write(response.getOutputStream());



            msgMap.put("code","200");
            msgMap.put("msg","");
            msgMap.put("data","");
        } catch (IOException e) {
            e.printStackTrace();
            msgMap.put("code","500");
            msgMap.put("msg","程序异常，请联系管理员！");
            msgMap.put("data","");
        }
        return JSON.toJSONString(msgMap);
    }

    /**
     * 创建中央财政拨款收入下级
     * @param sheet
     * @param Data
     * @param space
     */
    public void createZyczbksrXj(HSSFSheet sheet, List<BudgetSectionIncome2> Data, String space){
        if (Data == null){
            return ;
        }
        for (BudgetSectionIncome2 bean:Data) {
            HSSFRow czbksrRow = sheet.createRow(thisIndex);
            czbksrRow.setHeightInPoints(20);

            HSSFCell kmdm = czbksrRow.createCell(1);
            kmdm.setCellValue(bean.getKmmc());
            czbksrRow.createCell(1).setCellValue(space+bean.getKmmc());

            czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
            czbksrRow.createCell(3);
            //合并行上年
            CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regionSn);

            czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
            czbksrRow.createCell(5);
            //合并行本年
            CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regionBn);


            czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

            czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

            czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

            czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

            czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

            czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

            czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

            czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

            czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

            czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

            czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

            czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

            czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

            thisIndex++;
            List<BudgetSectionIncome2> xj = bean.getXj();
            createZyczbksrXj(sheet,xj,space+space);

            //设置表格中字体
            for (int k = 1; k < 19; k++) {
                if (k == 1)
                    czbksrRow.getCell(k).setCellStyle(fontStyle);
                else
                    czbksrRow.getCell(k).setCellStyle(fontCenterStyle);

            }

        }
    }

    /**
     * 创建非中央财政拨款收入项目
     * @param sheet
     * @param Data
     */
    public void createFzyczbksrXm(HSSFSheet sheet, List<BudgetSectionIncome2> Data){
        if (Data == null){
            return ;
        }
        for (BudgetSectionIncome2 bean:Data) {
            HSSFRow czbksrRow = sheet.createRow(thisIndex);
            czbksrRow.setHeightInPoints(20);

            HSSFCell kmdm = czbksrRow.createCell(1);
            kmdm.setCellValue(bean.getKmmc());
            czbksrRow.createCell(1).setCellValue(bean.getKmmc());

            czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
            czbksrRow.createCell(3);
            //合并行上年
            CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regionSn);

            czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
            czbksrRow.createCell(5);
            //合并行本年
            CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regionBn);


            czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

            czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

            czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

            czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

            czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

            czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

            czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

            czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

            czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

            czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

            czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

            czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

            czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

            czbksrRow.createCell(19).setCellValue(Double.parseDouble(bean.getBnsjsr()));

            thisIndex++;

            fzysnxj = fzysnxj.add(new BigDecimal(bean.getSnjz()));
            fzybnxj = fzybnxj.add(new BigDecimal(bean.getBnsr()));
            fzyzj = fzyzj.add(new BigDecimal(bean.getHj()));
            fzysnjzysapxj = fzysnjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
            fzybnsrysapxj = fzybnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
            fzyhj1zj = fzyhj1zj.add(new BigDecimal(bean.getHj1()));
            fzysnjzwapxj = fzysnjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
            fzybnsrwapxj = fzybnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
            fzyhj2zj = fzyhj2zj.add(new BigDecimal(bean.getHj2()));
            fzysnjzyszcxj = fzysnjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
            fzybnsryszcxj = fzybnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
            fzyhj3zj = fzyhj3zj.add(new BigDecimal(bean.getHj3()));
            fzysjsrzj = fzysjsrzj.add(new BigDecimal(bean.getBnsjsr()));
            //设置表格中字体
            for (int k = 1; k < 20; k++) {
                czbksrRow.getCell(k).setCellStyle(fontCenterStyle);
            }

        }
    }

    /**
     * 创建收入情况
     * @param sheet
     */
    public void createSrqk(HSSFSheet sheet){
        HSSFRow row = sheet.createRow(thisIndex);
        row.setHeightInPoints(20);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("收入单位");

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("财政拨款");
        CellRangeAddress regCzbk = new CellRangeAddress(thisIndex, thisIndex, 1, 2);
        sheet.addMergedRegion(regCzbk);

        HSSFCell cell3 = row.createCell(4);
        cell3.setCellStyle(cellStyle);
        cell3.setCellValue("非财政拨款");
        CellRangeAddress regFczbk = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
        sheet.addMergedRegion(regFczbk);

        HSSFCell cell7 = row.createCell(7);
        cell7.setCellStyle(cellStyle);
        cell7.setCellValue("总计");

        //-----------------------------------------------

        HSSFRow row1 = sheet.createRow(thisIndex + 1);
        row1.setHeightInPoints(20);
        HSSFCell cell1_0 = row1.createCell(0);

        HSSFCell cell1_1 = row1.createCell(1);
        cell1_1.setCellStyle(cellStyle);
        cell1_1.setCellValue("上年结转");

        HSSFCell cell1_2 = row1.createCell(2);
        cell1_2.setCellStyle(cellStyle);
        cell1_2.setCellValue("本年收入");

        HSSFCell cell1_3 = row1.createCell(3);
        cell1_3.setCellStyle(cellStyle);
        cell1_3.setCellValue("小计");

        HSSFCell cell1_4 = row1.createCell(4);
        cell1_4.setCellStyle(cellStyle);
        cell1_4.setCellValue("上年结转");

        HSSFCell cell1_5 = row1.createCell(5);
        cell1_5.setCellStyle(cellStyle);
        cell1_5.setCellValue("本年收入");

        HSSFCell cell1_6 = row1.createCell(6);
        cell1_6.setCellStyle(cellStyle);
        cell1_6.setCellValue("小计");

        HSSFCell cell1_7 = row1.createCell(7);

        CellRangeAddress regSrdw = new CellRangeAddress(thisIndex, thisIndex+1, 0, 0);
        sheet.addMergedRegion(regSrdw);

        CellRangeAddress regZj = new CellRangeAddress(thisIndex, thisIndex+1, 7, 7);
        sheet.addMergedRegion(regZj);

        //--------------------------------------------------------------

        HSSFRow endRow = sheet.createRow(thisIndex + 2);
        endRow.setHeightInPoints(20);
        HSSFCell endCell0 = endRow.createCell(0);
        endCell0.setCellStyle(fontCenterStyle);
        endCell0.setCellValue("拱北海关本级");

        HSSFCell endCell1 = endRow.createCell(1);
        endCell1.setCellStyle(fontCenterStyle);
        endCell1.setCellValue(Double.parseDouble(zysnxj.toString()));

        HSSFCell endCell2 = endRow.createCell(2);
        endCell2.setCellStyle(fontCenterStyle);
        endCell2.setCellValue(Double.parseDouble(zybnxj.toString()));

        HSSFCell endCell3 = endRow.createCell(3);
        endCell3.setCellStyle(fontCenterStyle);
        endCell3.setCellValue(Double.parseDouble(zyzj.toString()));

        HSSFCell endCell4 = endRow.createCell(4);
        endCell4.setCellStyle(fontCenterStyle);
        endCell4.setCellValue(Double.parseDouble(fzysnxj.toString()));

        HSSFCell endCell5 = endRow.createCell(5);
        endCell5.setCellStyle(fontCenterStyle);
        endCell5.setCellValue(Double.parseDouble(fzybnxj.toString()));

        HSSFCell endCell6 = endRow.createCell(6);
        endCell6.setCellStyle(fontCenterStyle);
        endCell6.setCellValue(Double.parseDouble(fzyzj.toString()));

        HSSFCell endCell7 = endRow.createCell(7);
        endCell7.setCellStyle(fontCenterStyle);
        endCell7.setCellValue(Double.parseDouble(zysnxj.add(zybnxj.add(zyzj.add(fzysnxj.add(fzybnxj.add(fzyzj))))).toString()));
    }
}
